-- =============================================
-- 教材管理系统数据库建表脚本 (SQL Server)
-- 创建日期: 2024年12月
-- =============================================

-- 创建数据库
USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'TextbookManagement')
    DROP DATABASE TextbookManagement;
GO

CREATE DATABASE TextbookManagement
ON 
( NAME = 'TextbookManagement_Data',
  FILENAME = 'C:\Data\TextbookManagement_Data.mdf',
  SIZE = 100MB,
  MAXSIZE = 1GB,
  FILEGROWTH = 10MB )
LOG ON 
( NAME = 'TextbookManagement_Log',
  FILENAME = 'C:\Data\TextbookManagement_Log.ldf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 1MB );
GO

USE TextbookManagement;
GO

-- =============================================
-- 1. 创建教材分类表
-- =============================================
CREATE TABLE Category (
    category_id INT IDENTITY(1,1) PRIMARY KEY,
    category_name NVARCHAR(50) NOT NULL,
    parent_id INT NULL,
    description NVARCHAR(200) NULL,
    create_time DATETIME NOT NULL DEFAULT GETDATE(),
    
    -- 外键约束：自引用
    CONSTRAINT FK_Category_Parent FOREIGN KEY (parent_id) 
        REFERENCES Category(category_id)
);

-- 创建索引
CREATE INDEX IX_Category_ParentId ON Category(parent_id);

-- =============================================
-- 2. 创建教材表
-- =============================================
CREATE TABLE Textbook (
    textbook_id INT IDENTITY(1,1) PRIMARY KEY,
    isbn NVARCHAR(20) NOT NULL UNIQUE,
    title NVARCHAR(200) NOT NULL,
    author NVARCHAR(100) NOT NULL,
    publisher NVARCHAR(100) NOT NULL,
    publish_date DATE NULL,
    edition NVARCHAR(20) NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INT NOT NULL,
    description NTEXT NULL,
    create_time DATETIME NOT NULL DEFAULT GETDATE(),
    update_time DATETIME NOT NULL DEFAULT GETDATE(),
    
    -- 外键约束
    CONSTRAINT FK_Textbook_Category FOREIGN KEY (category_id) 
        REFERENCES Category(category_id)
);

-- 创建索引
CREATE INDEX IX_Textbook_ISBN ON Textbook(isbn);
CREATE INDEX IX_Textbook_Title ON Textbook(title);
CREATE INDEX IX_Textbook_CategoryId ON Textbook(category_id);

-- =============================================
-- 3. 创建供应商表
-- =============================================
CREATE TABLE Supplier (
    supplier_id INT IDENTITY(1,1) PRIMARY KEY,
    supplier_name NVARCHAR(100) NOT NULL,
    contact_person NVARCHAR(50) NULL,
    phone NVARCHAR(20) NULL,
    email NVARCHAR(100) NULL,
    address NVARCHAR(200) NULL,
    status TINYINT NOT NULL DEFAULT 1 CHECK (status IN (0,1)),
    create_time DATETIME NOT NULL DEFAULT GETDATE()
);

-- 创建索引
CREATE INDEX IX_Supplier_Name ON Supplier(supplier_name);
CREATE INDEX IX_Supplier_Status ON Supplier(status);

-- =============================================
-- 4. 创建采购订单表
-- =============================================
CREATE TABLE Purchase_Order (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    order_no NVARCHAR(50) NOT NULL UNIQUE,
    supplier_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
    status TINYINT NOT NULL DEFAULT 1 CHECK (status IN (1,2,3,4)),
    remark NVARCHAR(500) NULL,
    create_time DATETIME NOT NULL DEFAULT GETDATE(),
    
    -- 外键约束
    CONSTRAINT FK_PurchaseOrder_Supplier FOREIGN KEY (supplier_id) 
        REFERENCES Supplier(supplier_id)
);

-- 创建索引
CREATE INDEX IX_PurchaseOrder_OrderNo ON Purchase_Order(order_no);
CREATE INDEX IX_PurchaseOrder_SupplierId ON Purchase_Order(supplier_id);
CREATE INDEX IX_PurchaseOrder_OrderDate ON Purchase_Order(order_date);
CREATE INDEX IX_PurchaseOrder_Status ON Purchase_Order(status);

-- =============================================
-- 5. 创建采购订单明细表
-- =============================================
CREATE TABLE Purchase_Order_Detail (
    detail_id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    textbook_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    subtotal DECIMAL(12,2) NOT NULL CHECK (subtotal >= 0),
    
    -- 外键约束
    CONSTRAINT FK_OrderDetail_Order FOREIGN KEY (order_id) 
        REFERENCES Purchase_Order(order_id) ON DELETE CASCADE,
    CONSTRAINT FK_OrderDetail_Textbook FOREIGN KEY (textbook_id) 
        REFERENCES Textbook(textbook_id)
);

-- 创建索引
CREATE INDEX IX_OrderDetail_OrderId ON Purchase_Order_Detail(order_id);
CREATE INDEX IX_OrderDetail_TextbookId ON Purchase_Order_Detail(textbook_id);

-- =============================================
-- 6. 创建库存表
-- =============================================
CREATE TABLE Inventory (
    inventory_id INT IDENTITY(1,1) PRIMARY KEY,
    textbook_id INT NOT NULL UNIQUE,
    current_stock INT NOT NULL DEFAULT 0 CHECK (current_stock >= 0),
    min_stock INT NOT NULL DEFAULT 0 CHECK (min_stock >= 0),
    max_stock INT NOT NULL DEFAULT 1000 CHECK (max_stock > 0),
    last_update DATETIME NOT NULL DEFAULT GETDATE(),
    
    -- 外键约束
    CONSTRAINT FK_Inventory_Textbook FOREIGN KEY (textbook_id) 
        REFERENCES Textbook(textbook_id),
    
    -- 检查约束：最大库存应大于最小库存
    CONSTRAINT CK_Inventory_Stock CHECK (max_stock >= min_stock)
);

-- 创建索引
CREATE INDEX IX_Inventory_TextbookId ON Inventory(textbook_id);
CREATE INDEX IX_Inventory_CurrentStock ON Inventory(current_stock);

-- =============================================
-- 7. 创建出入库记录表
-- =============================================
CREATE TABLE Stock_Record (
    record_id INT IDENTITY(1,1) PRIMARY KEY,
    textbook_id INT NOT NULL,
    record_type TINYINT NOT NULL CHECK (record_type IN (1,2)), -- 1:入库, 2:出库
    quantity INT NOT NULL CHECK (quantity > 0),
    record_date DATETIME NOT NULL DEFAULT GETDATE(),
    operator NVARCHAR(50) NOT NULL,
    remark NVARCHAR(200) NULL,
    
    -- 外键约束
    CONSTRAINT FK_StockRecord_Textbook FOREIGN KEY (textbook_id) 
        REFERENCES Textbook(textbook_id)
);

-- 创建索引
CREATE INDEX IX_StockRecord_TextbookId ON Stock_Record(textbook_id);
CREATE INDEX IX_StockRecord_RecordDate ON Stock_Record(record_date);
CREATE INDEX IX_StockRecord_RecordType ON Stock_Record(record_type);

-- =============================================
-- 8. 创建课程表
-- =============================================
CREATE TABLE Course (
    course_id INT IDENTITY(1,1) PRIMARY KEY,
    course_code NVARCHAR(20) NOT NULL UNIQUE,
    course_name NVARCHAR(100) NOT NULL,
    credit DECIMAL(3,1) NOT NULL CHECK (credit > 0),
    department NVARCHAR(50) NOT NULL,
    description NTEXT NULL,
    create_time DATETIME NOT NULL DEFAULT GETDATE()
);

-- 创建索引
CREATE INDEX IX_Course_CourseCode ON Course(course_code);
CREATE INDEX IX_Course_CourseName ON Course(course_name);
CREATE INDEX IX_Course_Department ON Course(department);

-- =============================================
-- 9. 创建课程教材关联表
-- =============================================
CREATE TABLE Course_Textbook (
    relation_id INT IDENTITY(1,1) PRIMARY KEY,
    course_id INT NOT NULL,
    textbook_id INT NOT NULL,
    semester NVARCHAR(20) NOT NULL,
    textbook_type TINYINT NOT NULL CHECK (textbook_type IN (1,2)), -- 1:主教材, 2:辅助教材
    required_quantity INT NOT NULL CHECK (required_quantity > 0),
    create_time DATETIME NOT NULL DEFAULT GETDATE(),
    
    -- 外键约束
    CONSTRAINT FK_CourseTextbook_Course FOREIGN KEY (course_id) 
        REFERENCES Course(course_id),
    CONSTRAINT FK_CourseTextbook_Textbook FOREIGN KEY (textbook_id) 
        REFERENCES Textbook(textbook_id),
    
    -- 唯一约束：同一课程同一学期不能有重复的主教材
    CONSTRAINT UK_CourseTextbook_Main UNIQUE (course_id, semester, textbook_type, textbook_id)
);

-- 创建索引
CREATE INDEX IX_CourseTextbook_CourseId ON Course_Textbook(course_id);
CREATE INDEX IX_CourseTextbook_TextbookId ON Course_Textbook(textbook_id);
CREATE INDEX IX_CourseTextbook_Semester ON Course_Textbook(semester);

-- =============================================
-- 创建触发器：自动更新库存
-- =============================================

-- 出入库记录插入时自动更新库存
CREATE TRIGGER TR_StockRecord_UpdateInventory
ON Stock_Record
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @textbook_id INT, @record_type TINYINT, @quantity INT;
    
    SELECT @textbook_id = textbook_id, @record_type = record_type, @quantity = quantity
    FROM inserted;
    
    -- 确保库存记录存在
    IF NOT EXISTS (SELECT 1 FROM Inventory WHERE textbook_id = @textbook_id)
    BEGIN
        INSERT INTO Inventory (textbook_id, current_stock, min_stock, max_stock)
        VALUES (@textbook_id, 0, 10, 1000);
    END
    
    -- 更新库存
    IF @record_type = 1 -- 入库
    BEGIN
        UPDATE Inventory 
        SET current_stock = current_stock + @quantity,
            last_update = GETDATE()
        WHERE textbook_id = @textbook_id;
    END
    ELSE IF @record_type = 2 -- 出库
    BEGIN
        UPDATE Inventory 
        SET current_stock = current_stock - @quantity,
            last_update = GETDATE()
        WHERE textbook_id = @textbook_id;
    END
END;
GO

-- 教材表更新时间触发器
CREATE TRIGGER TR_Textbook_UpdateTime
ON Textbook
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE Textbook 
    SET update_time = GETDATE()
    WHERE textbook_id IN (SELECT textbook_id FROM inserted);
END;
GO

-- 采购订单明细插入时自动计算小计
CREATE TRIGGER TR_OrderDetail_CalculateSubtotal
ON Purchase_Order_Detail
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE Purchase_Order_Detail 
    SET subtotal = quantity * unit_price
    WHERE detail_id IN (SELECT detail_id FROM inserted);
    
    -- 更新订单总金额
    UPDATE Purchase_Order 
    SET total_amount = (
        SELECT SUM(subtotal) 
        FROM Purchase_Order_Detail 
        WHERE order_id = Purchase_Order.order_id
    )
    WHERE order_id IN (SELECT DISTINCT order_id FROM inserted);
END;
GO

-- =============================================
-- 创建视图：库存预警视图
-- =============================================
CREATE VIEW V_InventoryAlert AS
SELECT 
    t.textbook_id,
    t.title,
    t.isbn,
    t.author,
    t.publisher,
    i.current_stock,
    i.min_stock,
    i.max_stock,
    CASE 
        WHEN i.current_stock <= i.min_stock THEN '库存不足'
        WHEN i.current_stock >= i.max_stock THEN '库存过多'
        ELSE '正常'
    END AS stock_status
FROM Textbook t
INNER JOIN Inventory i ON t.textbook_id = i.textbook_id
WHERE i.current_stock <= i.min_stock OR i.current_stock >= i.max_stock;
GO

-- =============================================
-- 创建视图：教材详细信息视图
-- =============================================
CREATE VIEW V_TextbookDetail AS
SELECT 
    t.textbook_id,
    t.isbn,
    t.title,
    t.author,
    t.publisher,
    t.publish_date,
    t.edition,
    t.price,
    c.category_name,
    i.current_stock,
    i.min_stock,
    i.max_stock,
    t.description,
    t.create_time,
    t.update_time
FROM Textbook t
LEFT JOIN Category c ON t.category_id = c.category_id
LEFT JOIN Inventory i ON t.textbook_id = i.textbook_id;
GO

-- =============================================
-- 创建存储过程：添加教材
-- =============================================
CREATE PROCEDURE SP_AddTextbook
    @isbn NVARCHAR(20),
    @title NVARCHAR(200),
    @author NVARCHAR(100),
    @publisher NVARCHAR(100),
    @publish_date DATE = NULL,
    @edition NVARCHAR(20) = NULL,
    @price DECIMAL(10,2),
    @category_id INT,
    @description NTEXT = NULL,
    @min_stock INT = 10,
    @max_stock INT = 1000
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @textbook_id INT;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 插入教材信息
        INSERT INTO Textbook (isbn, title, author, publisher, publish_date, edition, price, category_id, description)
        VALUES (@isbn, @title, @author, @publisher, @publish_date, @edition, @price, @category_id, @description);
        
        SET @textbook_id = SCOPE_IDENTITY();
        
        -- 初始化库存信息
        INSERT INTO Inventory (textbook_id, current_stock, min_stock, max_stock)
        VALUES (@textbook_id, 0, @min_stock, @max_stock);
        
        COMMIT TRANSACTION;
        
        SELECT @textbook_id AS textbook_id;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

-- =============================================
-- 创建存储过程：教材入库
-- =============================================
CREATE PROCEDURE SP_TextbookInStock
    @textbook_id INT,
    @quantity INT,
    @operator NVARCHAR(50),
    @remark NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 插入入库记录
        INSERT INTO Stock_Record (textbook_id, record_type, quantity, operator, remark)
        VALUES (@textbook_id, 1, @quantity, @operator, @remark);
        
        COMMIT TRANSACTION;
        
        SELECT '入库成功' AS message;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

-- =============================================
-- 创建存储过程：教材出库
-- =============================================
CREATE PROCEDURE SP_TextbookOutStock
    @textbook_id INT,
    @quantity INT,
    @operator NVARCHAR(50),
    @remark NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @current_stock INT;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 检查库存是否充足
        SELECT @current_stock = current_stock 
        FROM Inventory 
        WHERE textbook_id = @textbook_id;
        
        IF @current_stock < @quantity
        BEGIN
            RAISERROR('库存不足，当前库存：%d，需要出库：%d', 16, 1, @current_stock, @quantity);
            RETURN;
        END
        
        -- 插入出库记录
        INSERT INTO Stock_Record (textbook_id, record_type, quantity, operator, remark)
        VALUES (@textbook_id, 2, @quantity, @operator, @remark);
        
        COMMIT TRANSACTION;
        
        SELECT '出库成功' AS message;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

-- =============================================
-- 插入初始数据
-- =============================================

-- 插入教材分类
INSERT INTO Category (category_name, parent_id, description) VALUES
('计算机类', NULL, '计算机相关教材'),
('数学类', NULL, '数学相关教材'),
('英语类', NULL, '英语相关教材'),
('程序设计', 1, '程序设计类教材'),
('数据库', 1, '数据库相关教材'),
('网络技术', 1, '网络技术类教材'),
('高等数学', 2, '高等数学教材'),
('线性代数', 2, '线性代数教材'),
('大学英语', 3, '大学英语教材');

-- 插入供应商
INSERT INTO Supplier (supplier_name, contact_person, phone, email, address, status) VALUES
('清华大学出版社', '张三', '010-12345678', 'zhangsan@tup.com', '北京市海淀区清华大学', 1),
('人民邮电出版社', '李四', '010-87654321', 'lisi@ptpress.com', '北京市丰台区成寿寺路', 1),
('机械工业出版社', '王五', '010-11111111', 'wangwu@cmpbook.com', '北京市西城区百万庄', 1),
('高等教育出版社', '赵六', '010-22222222', 'zhaoliu@hep.com', '北京市西城区德外大街', 1);

-- 插入教材
INSERT INTO Textbook (isbn, title, author, publisher, publish_date, edition, price, category_id, description) VALUES
('9787302123456', '数据库系统概论', '王珊,萨师煊', '高等教育出版社', '2023-01-01', '第6版', 45.00, 5, '经典数据库教材'),
('9787115234567', 'Java程序设计', '张三', '人民邮电出版社', '2023-02-01', '第3版', 52.00, 4, 'Java编程入门教材'),
('9787111345678', '计算机网络', '谢希仁', '电子工业出版社', '2023-03-01', '第8版', 48.00, 6, '计算机网络经典教材'),
('9787302456789', '高等数学', '同济大学', '高等教育出版社', '2023-04-01', '第7版', 42.00, 7, '高等数学标准教材'),
('9787115567890', '大学英语综合教程', '李明', '外语教学与研究出版社', '2023-05-01', '第4版', 38.00, 9, '大学英语综合教材');

-- 插入课程
INSERT INTO Course (course_code, course_name, credit, department, description) VALUES
('CS001', '数据库原理与应用', 3.0, '计算机学院', '数据库系统的基本原理和应用'),
('CS002', 'Java程序设计', 4.0, '计算机学院', 'Java编程语言的学习和应用'),
('CS003', '计算机网络', 3.0, '计算机学院', '计算机网络的基本原理和技术'),
('MATH001', '高等数学', 5.0, '数学学院', '高等数学基础课程'),
('ENG001', '大学英语', 2.0, '外语学院', '大学英语综合能力培养');

-- 插入课程教材关联
INSERT INTO Course_Textbook (course_id, textbook_id, semester, textbook_type, required_quantity) VALUES
(1, 1, '2024春', 1, 120),
(2, 2, '2024春', 1, 150),
(3, 3, '2024春', 1, 100),
(4, 4, '2024春', 1, 200),
(5, 5, '2024春', 1, 180);

-- 初始化库存（通过存储过程）
EXEC SP_TextbookInStock @textbook_id = 1, @quantity = 100, @operator = '系统初始化', @remark = '初始库存';
EXEC SP_TextbookInStock @textbook_id = 2, @quantity = 120, @operator = '系统初始化', @remark = '初始库存';
EXEC SP_TextbookInStock @textbook_id = 3, @quantity = 80, @operator = '系统初始化', @remark = '初始库存';
EXEC SP_TextbookInStock @textbook_id = 4, @quantity = 150, @operator = '系统初始化', @remark = '初始库存';
EXEC SP_TextbookInStock @textbook_id = 5, @quantity = 130, @operator = '系统初始化', @remark = '初始库存';

PRINT '教材管理系统数据库创建完成！';
GO 